

create database VanChuyen
use VanChuyen
go

CREATE TABLE LoaiNhanVien
(
Ma varchar(255) not null primary key,
TenLoai varchar(255),
)

CREATE TABLE ChucVu
(
Ma varchar(255) not null primary key,
Ten varchar(255),
MaLoaiNhanVien varchar(255) FOREIGN KEY REFERENCES LoaiNhanVien(Ma),
)


CREATE TABLE TinhThanh
(
Ma varchar(255) not null primary key,
Ten varchar(255),
)


CREATE TABLE TuyenDuong
(
Ma varchar(255) not null primary key,
Ten varchar(255),
DiemDau varchar(255),
DiemCuoi varchar(255),
KhoangCach float,
ChiPhiDuKien float,
ThoiGianDuKien float,
)


CREATE TABLE DiemTrungGian
(
Ma varchar(255) not null primary key,
MaTuyenDuong varchar(255) FOREIGN KEY REFERENCES TuyenDuong(Ma),
MaChiNhanh varchar(255) FOREIGN KEY REFERENCES ChiNhanh(Ma),
)
CREATE TABLE KhachHang
(
Ma varchar(255) not null primary key,
Ten varchar(255),
UserName varchar(255),
MatKhau varchar(255),
CMND varchar(255),
SDT varchar(255),
Email varchar(255),
NgayDangKy datetime,
)
CREATE TABLE ChiNhanh
(
Ma varchar(255)  not null primary key,
Ten varchar(255),
DiaChi varchar(255),
SDT varchar(255),
MaTinhThanh varchar(255) FOREIGN KEY REFERENCES TinhThanh(Ma),
)

CREATE TABLE NhanVien
(
Ma varchar(255) not null primary key,
Ten varchar(255),
CMND varchar(255),
DiaChi varchar(255),
SDT varchar(255),
MaChucVu varchar(255) FOREIGN KEY REFERENCES ChucVu(Ma),
MaChiNhanh varchar(255) FOREIGN KEY REFERENCES ChiNhanh(Ma),
ThamNien int,
)
CREATE TABLE SuKienNhanVien
(
Ma varchar(255) not null primary key,
Ngay datetime,
SuKien varchar(255),
MaNhanVien varchar(255) FOREIGN KEY REFERENCES NhanVien(Ma),
)
CREATE TABLE Xe
(
Ma varchar(255) not null primary key,
LoaiXe varchar(255),
HangSanXuat varchar(255),
NgayMua datetime,
MaTinhTrang	varchar(255 ) FOREIGN KEY REFERENCES TinhTrangXe(Ma) ,
BienSo varchar(255),
SucChua float,
SoGioVanChuyen float,
SoQuanDuongVanChuyen float,
)
CREATE TABLE TinhTrangXe
(
Ma varchar(255) not null primary key,
Ten varchar(255),
)
CREATE TABLE SuKienXe
(
Ma varchar(255) not null primary key,
Ngay datetime,
SuKien varchar(255),
MaXe varchar(255)FOREIGN KEY REFERENCES Xe(Ma),

)
CREATE TABLE Kho
(
Ma varchar(255) not null primary key,
MaChiNhanh varchar(255) FOREIGN KEY REFERENCES ChiNhanh(Ma),
TinhTrang varchar,
)
CREATE TABLE GoiHang
(
Ma varchar(255) not null primary key,
TrongLuong float,
KichThuoc float,
--ViTriHienTai 
)

